import json
import mysql.connector
from my_json import save_json

my_json = {
    "data": [['物理', '历史', '化学'], ['物理', '历史', '政治'], ['物理', '历史', '生物'], ['物理', '历史', '地理'],
             ['物理', '化学', '政治'], ['物理', '化学', '生物'], ['物理', '化学', '地理'], ['物理', '政治', '生物'],
             ['物理', '政治', '地理'], ['物理', '生物', '地理'], ['历史', '化学', '政治'], ['历史', '化学', '生物'],
             ['历史', '化学', '地理'], ['历史', '政治', '生物'], ['历史', '政治', '地理'], ['历史', '生物', '地理'],
             ['化学', '政治', '生物'], ['化学', '政治', '地理'], ['化学', '生物', '地理'], ['政治', '生物', '地理']]
}


def count_choose():
    con = mysql.connector.connect(
        host="192.168.80.190",
        port=3306,
        user="hive",
        password="12345678",
        database="gaokao"
    )
    cursor = con.cursor()
    q = "select province_name,year,require_json,count(*) as num from grade_plan where is_grade=1 or year='2023' GROUP BY province_name, year, require_json"
    cursor.execute(q)
    results = cursor.fetchall()
    for r_item in results:
        gen_result(r_item)
    save_json("../sql/count_choose.json", my_json)


def gen_result(r_item):
    province_name = r_item[0]
    year = r_item[1]
    require_json = r_item[2]
    num = r_item[3]
    require_json = json.loads(require_json)
    r = require_json["r"]
    n = require_json["n"]
    o = require_json["o"]
    if province_name not in my_json:
        my_json[province_name] = {}
    if year not in my_json[province_name]:
        my_json[province_name][year] = {}
    for j in range(len(my_json["data"])):
        if conform(my_json["data"][j], r, n, o):
            k = str(j)
            if k not in my_json[province_name][year]:
                my_json[province_name][year][k] = 0
            my_json[province_name][year][k] += num


def conform(l, r, n, o):
    for i in r:
        if i not in l:
            return False
    if n <= 0:
        return True
    for i in o:
        if i in l:
            if n <= 1:
                return True
            n -= 1
    return False


def update():
    g = {}
    for k, v_province in my_json.items():
        if k == "data":
            continue
        for y, v in v_province.items():
            if y not in g:
                g[y] = {}
            try:
                wl = v["5"] + v["14"]
                wl = wl / sum(v.values())
                if wl > 0.9:
                    v = {"5": v["5"], "14": v["14"]}
                    my_json[k][y] = v
                    continue
            except:
                pass
            try:
                wl = v["0"] + v["1"]+v["2"]+v["3"]
                wl = wl / sum(v.values())
                if wl > 0.35:
                    v["0"] = v["1"] = v["2"] = v["3"] = 0
            except:
                pass
            for type, num in v.items():
                if type not in g[y]:
                    g[y][type] = 0
                g[y][type] += num
            # 按字典v的值排序
            v = dict(sorted(v.items(), key=lambda item: item[1], reverse=True)[:6])
            my_json[k][y] = v
            g[y] = dict(sorted(g[y].items(), key=lambda item: item[1], reverse=True)[:6])
    my_json["全国"] = g
    save_json("../sql/count_choose.json", my_json)


count_choose()
update()
